/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package encaps.entities;

import java.lang.Long;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.FinderException;
import javax.ejb.NoSuchEntityException;
import javax.ejb.ObjectNotFoundException;
import javax.ejb.RemoveException;
import javax.naming.NamingException;
import javax.sql.DataSource;
import encaps.utils.DatabaseUtil;
import encaps.utils.StringUtil;
/**
 *
 * @author Artem
 */
public class Movie implements EntityBean {
    
    public static final String TABLE_NAME = "movie";
    public static final String NAME = "title";
    public static final String DESCRIPTION = "description";
    public static final String PIC = "pic";
    public static final String RATING = "rating";
    
    private EntityContext context;
    private DataSource dataSource;
    private boolean isEdited;
   
    private Long id;
    private String name;
    private String picture;
    private String descript;
    private Double rating;
    private HashSet genres;
    private HashMap comments;

    public void setEntityContext(EntityContext aContext) {
        context = aContext;
        try {
            dataSource = DatabaseUtil.getDataSource();
            isEdited = false;
        } catch(NamingException e) {
            throw new EJBException("DataSource lookup error", e);
        }
    }
    
    /**
     * @see javax.ejb.EntityBean#ejbActivate()
     */
    public void ejbActivate() {
        
    }

    /**
     * @see javax.ejb.EntityBean#ejbPassivate()
     */
    public void ejbPassivate() {
        
    }

    /**
     * @see javax.ejb.EntityBean#ejbRemove()
     */
    public void ejbRemove() throws RemoveException  {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("DELETE FROM movie WHERE id=?");
            ps.setLong(1, id);
            if(ps.executeUpdate() < 1) {
                throw new RemoveException("Movie delete error. Id="+id);
            }
            isEdited = false;
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.ejbRemove method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
        
    }
    
   public Long ejbCreate(String name, String picture, String descript, HashSet genres) throws CreateException {
        PreparedStatement insertValuesQuery = null;
        PreparedStatement selectIdQuery = null;
        Connection conn = null;
        try {
            this.picture = picture;
            this.name = name;
            this.descript = descript;
            this.genres = genres;
            conn = dataSource.getConnection();
            insertValuesQuery = conn.prepareStatement("INSERT INTO movie(title, pic, description) VALUES(?, ?, ?)");
            insertValuesQuery.setString(1, name);
            insertValuesQuery.setString(2, picture);
            insertValuesQuery.setString(3, descript);
            if(insertValuesQuery.executeUpdate() != 1) {
                throw new CreateException("Insert error");
            }
            selectIdQuery = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            ResultSet rs = selectIdQuery.executeQuery();
            if(!rs.next()) {
                throw new CreateException("Insert error");
            }
            id = rs.getLong(1);
/**  TODO: while(genres.next) { CONSTRUCT INSERT QUERY }
            insertValuesQuery = conn.prepareStatement("INSERT INTO movie_genres(movie_id, genre) VALUES(?, ?)");
            insertValuesQuery.setLong(1, id);
            insertValuesQuery.setString(1, "genre");
   */         
            
            isEdited = false;
            return id;
        } catch(Exception ex) {
            throw new EJBException("Error in Movie.ejbCreate method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, selectIdQuery);
            DatabaseUtil.closeConnection(null, insertValuesQuery);
        }

    }
    
    public void ejbPostCreate(String name, String picture, String descript, HashSet genres) throws CreateException {
    }
  
    /**
     * @see javax.ejb.EntityBean#unsetEntityContext()
     */
    public void unsetEntityContext() {
        context = null;
    }

    /**
     * @see javax.ejb.EntityBean#ejbLoad()
     */
    public void ejbLoad() {
        id = (Long) context.getPrimaryKey();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT title, description, pic, rating FROM movie WHERE id=?");
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            if(!rs.next()) {
                throw new NoSuchEntityException("No movie found by id = " + id);
            }
            picture = rs.getString(PIC);
            name = rs.getString(NAME);
            descript = rs.getString(DESCRIPTION);
            rating= rs.getDouble(RATING);
            ps = conn.prepareStatement("SELECT genre FROM movie_genre WHERE movie_id=?");
            ps.setLong(1, id);
            rs = ps.executeQuery();
            genres = new HashSet();
            while (rs.next()) {
                genres.add(rs.getString("genre"));
            }
            ps = conn.prepareStatement("SELECT id, user_id, content, date FROM comment WHERE movie_id=?");
            ps.setLong(1, id);
            rs = ps.executeQuery();
            comments = new HashMap();
            Comment c;
            while (rs.next()) {
                Long key=rs.getLong("id");
                c = new Comment(key, rs.getLong("user_id"), rs.getString("content"),rs.getDate("date"));
//                c.setEditDate(rs.getDate(editDate));
                comments.put(key, c);
            }
            isEdited = false;
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.ejbLoad method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    /**
     * @see javax.ejb.EntityBean#ejbStore()
     */
    public void ejbStore() {
        if(!isEdited) {
            return;
        }
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            if (isEdited) {
                ps = conn.prepareStatement("UPDATE movie SET title=?, pic=?, description=? WHERE id=?");
                ps.setString(1, name);
                ps.setString(2, picture);
                ps.setString(3, descript);
                ps.setLong(4, id);
                if(ps.executeUpdate() < 1) {
                    throw new NoSuchEntityException("No movie found by id = " + id);
                }
            isEdited = false;
            }
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.ejbStore method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public Collection ejbFindAll() {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT id FROM Movie");
            Collection keys = new LinkedList();
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                keys.add(rs.getLong(1));
            }
            return keys;
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.ejbFindAll method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }
    
    // </editor-fold>
    /**
     * See EJB 2.0 and EJB 2.1 section 12.2.5
     */
    public java.lang.Long ejbFindByPrimaryKey(java.lang.Long key) throws FinderException {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("SELECT id FROM movie WHERE id=?;");
            ps.setLong(1, key);
            ResultSet rs = ps.executeQuery();
            if(!rs.next()) {
                throw new ObjectNotFoundException("No movie found by primary key = " + key);
            }
            return key;
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.ejbFindByPrimaryKey method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }
 
    
    public Long getId() {
        return id;
    }
    
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
        isEdited = true;
    }

    public String getPicture() {
        return picture;
    }

    public void setPicture(String picture) {
        this.picture = picture;
        isEdited = true;
    }

    public String getDecription() {
        return descript;
    }

    public void setDecription(String descript) {
        this.descript = descript;
        isEdited = true;
    }
    
    public void setGenre(HashSet genre) {
        PreparedStatement insertValuesQuery = null;
        PreparedStatement selectIdQuery = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            
            /**  TODO: while(genres.next) { CONSTRUCT INSERT QUERY }
            insertValuesQuery = conn.prepareStatement("INSERT INTO movie_genres(movie_id, genre) VALUES(?, ?)");
            insertValuesQuery.setLong(1, id);
            insertValuesQuery.setString(1, "genre");
   */         
            genres.add(genre);
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.setGenre method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, selectIdQuery);
            DatabaseUtil.closeConnection(null, insertValuesQuery);
        }

    }
    
    public String getGenres() {
        String g=genres.toString();
        return g.substring(1, g.length()-1);
    }
    
    public String stringRepresentation() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getName());
        sb.append(": [");
        sb.append("id=");
        sb.append(id);
        sb.append(", name=");
        sb.append(name);
        sb.append(", descript=");
        sb.append(descript);
        sb.append(", picture=");
        sb.append(picture);
        sb.append(", genres=");
        sb.append(getGenres());
        sb.append(", rating=");
        sb.append(rating);
        sb.append("]");
        return sb.toString();
    }
    
    public void addComment(Long user_id, String content, Date date) throws CreateException {
        PreparedStatement insertValuesQuery = null;
        PreparedStatement selectIdQuery = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
/**            SimpleDateFormat formatter = new SimpleDateFormat();
            formatter.applyPattern("yyyy-MM-dd HH:mm:ss");
            String dateTime = formatter.format(date.getTime());
 */
            insertValuesQuery = conn.prepareStatement("INSERT INTO COMMENT(user_id, movie_id, content, date) " 
							    + "VALUES(?, ?, ?, ?)");
            insertValuesQuery.setLong(1, user_id);
            insertValuesQuery.setLong(2, id);
            insertValuesQuery.setString(3, content);
            insertValuesQuery.setDate(4, new java.sql.Date(date.getTime()));
            if (insertValuesQuery.executeUpdate() != 1) {
                    throw new CreateException("Insertion error");
            }
            selectIdQuery = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            ResultSet rs = selectIdQuery.executeQuery();
            if(!rs.next()) {
                throw new CreateException("Insertion error 2");
            }
            Long key = rs.getLong(1);
            comments.put(key, new Comment(key, user_id, content, date));
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.addComment method", ex);
        }  finally {
            DatabaseUtil.closeConnection(conn, selectIdQuery);
            DatabaseUtil.closeConnection(null, insertValuesQuery);
        }
    }

    public void removeComment(Long comment_id) throws RemoveException {    
        PreparedStatement ps = null;
        Connection conn = null;        
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("DELETE FROM comment WHERE id=?");
            ps.setLong(1, comment_id);
            if(ps.executeUpdate() < 1) {
                 throw new NoSuchEntityException("No comment found by id = " + comment_id);
            }
            comments.remove(comment_id);
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.removeComment method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }  

    public void updateComment(Long comment_id, String content, Date date) {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            SimpleDateFormat formatter = new SimpleDateFormat();
/**            formatter.applyPattern("yyyy-MM-dd HH:mm:ss");
            String dateTime = formatter.format(date.getTime());
            ps = conn.prepareStatement("UPDATE comment SET content=?, editDate=? WHERE id=?");            */
            ps.setString(1, content);
            ps.setDate(2, new java.sql.Date(date.getTime()));
            ps.setLong(3, comment_id);
            if(ps.executeUpdate() < 1) {
                 throw new NoSuchEntityException("No comment found by id = " + comment_id);
            }
        Comment editedComment = (Comment) comments.get(comment_id);
        editedComment.setContent(content);
        editedComment.setEditDate(date);
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.updateComment method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }
    
    public HashMap getComments(){
        return comments;
    }
    
    public Double getRating() {
        return rating;
    }
    
    public void addRating(Double rating, Long user_id, Date date) throws CreateException {
        PreparedStatement insertValuesQuery = null;
        PreparedStatement selectMovieRateQuery = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
/**            SimpleDateFormat formatter = new SimpleDateFormat();
            formatter.applyPattern("yyyy-MM-dd HH:mm:ss");
            String dateTime = formatter.format(date.getTime()); */
            insertValuesQuery = conn.prepareStatement("INSERT INTO USER_RATING(user_id, movie_id, rating, date) " 
							    + "VALUES(?, ?, ?, ?)");
            insertValuesQuery.setLong(1, user_id);
            insertValuesQuery.setLong(2, id);
            insertValuesQuery.setDouble(3, rating);
            insertValuesQuery.setDate(4, new java.sql.Date(date.getTime()));
            if (insertValuesQuery.executeUpdate() != 1) {
                throw new CreateException("Insertion error");
            }
            selectMovieRateQuery = conn.prepareStatement("SELECT rating FROM movie WHERE id=?;");
            selectMovieRateQuery.setLong(1, id);
            ResultSet rs = selectMovieRateQuery.executeQuery();
            if(!rs.next()) {
                throw new CreateException("Insertion error");
            }
            this.rating= rs.getDouble(1);
        } catch(SQLException ex) {
            throw new EJBException("Error in Movie.addRating method", ex);
        } finally {
            DatabaseUtil.closeConnection(conn, selectMovieRateQuery);
            DatabaseUtil.closeConnection(null, insertValuesQuery);
        }        
    }
}
